<?php
/**
 * This file contains the class AlertsCheckDB which provides
 * all of the database requirements for checking alerts
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 * @version    $Id:$
 */
/**
 * This class provides access to the database for AlertsCheckDB
 *
 * @copyright  2009 A Memory Tree
 * @license    A Memory Tree License
 * @version    Release: @package_version@
 */
class AlertsCheckDB
{
    /**
      * Provides access to the database
     *
     * @var Db
     */
    private $_db;
    /**
     * Create a new instance of this class
     * 
     * @return void
     * @param Db $db
     */
    function __construct($db)
    {
        $this->_db = $db;
    }
    /**
         * Escape the provided value
         *
         * @return string
         * @param mixed $value
         */
    function escape($value)
    {
        $db = $this->_db;
        return $db->escape($value);
    }
    /**
     * Get all of the active paid for alerts
     *
     * @return array[string]string
     * @param int $personId
     */
    function getPerson($personId)
    {
        $db = $this->_db;
        $person = array(
            'firstName' => '',
            'alsoKnownAs' => '',
            'lastName' => '',
            'formerName' => '',
            'neeName' => ''
        );
        $alerts = array(
            0 => $person
        );
        $sql = <<< EOT
            SELECT FirstNames, AlsoKnownAs, Surname, FormerName, NeeName
            FROM Person
            WHERE
                Id = %d
EOT;
        $sql = sprintf($sql, intval($personId));
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $person['firstName'] = $db->unescape($row['FirstNames']);
            $person['alsoKnownAs'] = $db->unescape($row['AlsoKnownAs']);
            $person['lastName'] = $db->unescape($row['Surname']);
            $person['formerName'] = $db->unescape($row['FormerName']);
            $person['neeName'] = $db->unescape($row['NeeName']);
        }
        return $person;
    }
    /**
     * Get all of the active paid for alerts
     *
     * @return array[int][string]string
     * @param int $personId
     */
    function getUnemailedPaidAlerts($personId)
    {
        $db = $this->_db;
        $alert = array(
                'alertId' => '',
                'userId' => '',
                'firstName' => '',
                'lastName' => ''
        );
        $alerts = array(
            0 => $alert
        );
        $sql = <<< EOT
            SELECT *
            FROM
                ActivePaidAlerts
                    LEFT OUTER JOIN
              ( SELECT AlertId, PersonId
                FROM EmailedPersonAlerts
                WHERE PersonId = %d
                ) AS PersonAlerts
                    ON AlertId = ActivePaidAlerts.Id
            WHERE
                AlertId IS NULL
EOT;
        $sql = sprintf($sql, $personId);
        $result = $db->query($sql);
        $rowCount = $db->rowCount($result);
        for ($i = 0; $i < $rowCount; $i++) {
            $row = $db->fetch($result);
            $alert['alertId'] = $db->unescape($row['Id']);
            $alert['userId'] = $db->unescape($row['UserId']);
            $alert['firstName'] = $db->unescape($row['FirstName']);
            $alert['lastName'] = $db->unescape($row['LastName']);
            $alert['purchased'] = $db->unescape($row['Purchased']);
            $alerts[$i] = $alert;
        }
        return $alerts;
    }
    /**
     * Get email address for a user
     *
     * @return string
     * @param int $userId
     */
    function getEmail($userId)
    {
        $db = $this->_db;
        $email = '';
        $sql = <<< EOT
            SELECT Email
            FROM Users
            WHERE
                ID = %d
EOT;
        $sql = sprintf($sql, $userId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $row = $db->fetch($result);
            $email = $db->unescape($row['Email']);
        }
        return $email;
    }
    /**
     * Set a persons alert as already being notified
     *
     * @return bool
     * @param int $personId
     * @param int $alertId
     */
    function setPersonAlert($personId, $alertId)
    {
        $db = $this->_db;
        $sql = <<< EOT
            INSERT INTO
                EmailedPersonAlerts(AlertId, PersonId)
                VALUES(%d, %d)
EOT;
        $sql = sprintf($sql, $alertId, $personId);
        $result = $db->query($sql);
        return $result === TRUE;
    }
    /**
     * Check if a person has been emailed about an alert
     *
     * @return bool
     * @param int $personId
     * @param int $alertId
     */
    function hasPersonAlert($personId, $alertId)
    {
        $db = $this->_db;
        $hasPersonAlert = FALSE;
        $sql = <<< EOT
            SELECT AlertId, PersonId
            FROM EmailedPersonAlerts
            WHERE
                AlertId = %d AND
                PersonId = %d
EOT;
        $sql = sprintf($sql, $alertId, $personId);
        $result = $db->query($sql);
        if ($db->rowCount($result) > 0) {
            $hasPersonAlert = TRUE;
        }
        return $hasPersonAlert;
    }
}
?>